package com.imooc.framework.excel;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Calendar;

public class MyExcelOutput {
    public static void main(String[] args) throws IOException {
        //创建工作簿
//        Workbook workbook = new HSSFWorkbook(); //XLS后缀对象，速度快，但最多只能写65536条数据
        Workbook workbook =new XSSFWorkbook(); //xlsx后缀对象
//        XSSF
        //创建sheet
        Sheet sheet = workbook.createSheet("sheet名");

//        创建行
        Row row1 = sheet.createRow(0);//第一行
        Row row2 = sheet.createRow(1);//第二行
        Row row3 = sheet.createRow(2);//第三行

//        设置单元格格式
        CellStyle cellStyle = workbook.createCellStyle();//单元格格式只能通过工作簿对象进行操作
//        设置水平对齐的样式为居中对齐;
//        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//可能会因为POI版本问题导致报错
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//Horizontal Alignment 水平  对齐
        //设置垂直对齐的样式为居中对齐;
//        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//同样会因为版本问题报错
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        cellStyle.setFillPattern(FillPatternType.FINE_DOTS);
//
//        //创建单元格
//        Cell rowcell1 = row1.createCell(0);//A1第一行创建第一个单元格，row1是第一行对象
//        rowcell1.setCellValue("姓名");//赋值
//        rowcell1.setCellStyle(cellStyle);//赋予样式
//        Cell rowcell2 = row1.createCell(1);//A2第一行创建第二个单元格
//        rowcell2.setCellValue("年龄");
//        rowcell2.setCellStyle(cellStyle);
//        Cell rowcell3 = row1.createCell(2);//A3
//        rowcell3.setCellValue("课程");
//        rowcell3.setCellStyle(cellStyle);
//
//        Cell row2Cell1=row2.createCell(0); //单元格2-1，row2是第二行对象
//        row2Cell1.setCellValue("小明");
//        row2Cell1.setCellStyle(cellStyle);
//        Cell row2Cell2=row2.createCell(1);//单元格2-2
//        row2Cell2.setCellValue(16);
//        row2Cell2.setCellStyle(cellStyle);
//        Cell row2Cell3=row2.createCell(2);//单元格2-3
//        row2Cell3.setCellValue("语文");
//        row2Cell3.setCellStyle(cellStyle);
//        Cell row2Cell4=row2.createCell(3);//单元格2-4
//        row2Cell4.setCellValue("数学");
//        row2Cell4.setCellStyle(cellStyle);
//
//        //合并单元格（起始行，结束行，起始列，结束列）
//        CellRangeAddress cellAddresses = new CellRangeAddress(0,0,2,3);
//        sheet.addMergedRegion(cellAddresses);//sheet对象操作单元格合并  Merged Region 合并 区域
//
//        //要注意的是如果是行与行的合并，必须对第一行中的单元格赋值
//        Cell row1to2Cell5=row1.createCell(4);
//        CellRangeAddress timeRegion=new CellRangeAddress(0,1,4,4);
//        sheet.addMergedRegion(timeRegion);
//
//
//        String dateTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(Calendar.getInstance().getTime());
//        System.out.println(dateTime);
//        row1to2Cell5.setCellValue(dateTime);
//        row1to2Cell5.setCellStyle(cellStyle);

//        PrintStream printStream = new PrintStream(new FileOutputStream("I:\\TEST.xls"));

        CellRangeAddress cellAddresses = new CellRangeAddress(0,0,0,8);
        sheet.addMergedRegion(cellAddresses);

        CellRangeAddress cellAddresses1 = new CellRangeAddress(1,2,0,0);
        sheet.addMergedRegion(cellAddresses1);

        CellRangeAddress cellAddresses2 = new CellRangeAddress(1,1,1,4);
        sheet.addMergedRegion(cellAddresses2);

        CellRangeAddress cellAddresses3 = new CellRangeAddress(1,1,5,8);
        sheet.addMergedRegion(cellAddresses3);



        for (int i = 0; i < 9; i++) {
            Cell row1Cell = row1.createCell(i);
            if(i == 0){
                row1Cell.setCellValue("年份");
            }else {
                row1Cell.setCellValue("");
            }
            row1Cell.setCellStyle(cellStyle);

            Cell row2Cell = row2.createCell(i);
            if(i == 0){
                row2Cell.setCellValue("月份");
            } else if (i == 1 || i == 5) {
                row2Cell.setCellValue("窗口");
            }else {
                row2Cell.setCellValue("");
            }
            row2Cell.setCellStyle(cellStyle);

            Cell row3Cell = row3.createCell(i);
            if(i == 0){
                row3Cell.setCellValue("");
            } else if (i % 1 == 0) {
                row3Cell.setCellValue("测试1");
            } else if (i % 2 == 0) {
                row3Cell.setCellValue("测试2");
            } else if (i % 3 == 0) {
                row3Cell.setCellValue("测试3");
            } else if (i % 4 == 0) {
                row3Cell.setCellValue("测试4");
            }
            row3Cell.setCellStyle(cellStyle);
        }

        PrintStream printStream = new PrintStream(new FileOutputStream("D:\\TEST4.xlsx"));
        workbook.write(printStream);
        printStream.close();
        workbook.close();
//        ((SXSSFWorkbook)workbook).dispose();//xlsx内存溢出解决方法
    }
}

